package org.emet.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.time.DateUtils;
import org.emet.Constants;
import org.emet.entity.biz.Hall;
import org.emet.entity.biz.HallRent;
import org.emet.jee.core.dao.Page;
import org.emet.jee.core.dao.arch.ArchDataSourceBaseDao;
import org.emet.service.dto.DatePair;
import org.emet.service.dto.HallCurrStatus;
import org.emet.service.dto.HallRentCriteria;
import org.emet.service.dto.HallStatisticsDisplayItem;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class HallRentDao extends ArchDataSourceBaseDao<HallRent, Long> {
	@Autowired
	private HallDao hallDao;

	@Override
	public void save(HallRent entity) {
		entity.setStartDate(DateUtils.setSeconds(entity.getStartDate(), 0));
		entity.setEndDate(DateUtils.setSeconds(entity.getEndDate(), 0));
		if (entity.getEndDate() != null) {
			entity.setEndDate(DateUtils.setSeconds(entity.getEndDate(), 0));
		}
		super.save(entity);
	}

	public Page<HallRent> search(Page<HallRent> page, HallRentCriteria criteria) {
		Criteria c = getSession().createCriteria(HallRent.class);
		NonEmptyValueHashMap<String, Object> propertyNameValues = new NonEmptyValueHashMap<String, Object>();
		//Registration start
		propertyNameValues.put("registration.bizNo", criteria.getBizNo());
		propertyNameValues.put("registration.deadName", criteria.getDeadName());
		propertyNameValues.put("registration.dpyName", criteria.getDpyName());
		propertyNameValues.put("registration.carriageDate", criteria.getCarriageDate());
		propertyNameValues.put("registration.vehicleLpn", criteria.getVehicleLpn());
		propertyNameValues.put("registration.client", criteria.getClient());
		propertyNameValues.put("registration.cPyName", criteria.getcPyName());
		propertyNameValues.put("registration.phone", criteria.getPhone());
		//Registration end

		propertyNameValues.put("hall", criteria.getHall());
		propertyNameValues.put("startDate", criteria.getStartDate());
		propertyNameValues.put("endDate", criteria.getEndDate());
		propertyNameValues.put("worker", criteria.getWorker());
		propertyNameValues.put("remark", criteria.getRemark());
		c.add(Restrictions.allEq(propertyNameValues));
		page.setOrderBy("id");
		page.setOrder("desc");
		return findPage(page, c);
	}

	public List<DatePair> getBusyTimeRange(Long id, Long hallId) {
		String hql = " select new org.emet.service.dto.DatePair(startDate,endDate) from "
				+ entityClass.getSimpleName() + " where hallId = ?";

		if (id != null) {
			hql += " and id <> ?";
		}
		hql += " and endDate > now() order by startDate asc";
		if (id != null)
			return find(hql, hallId, id);
		else
			return find(hql, hallId);
	}

	public List<DatePair> getIdleTimeRange(Long hallRentId, Long hallId) {
		String hql = " select new org.emet.service.dto.DatePair(startDate,endDate) from "
				+ entityClass.getSimpleName() + " where hallId = ?";

		if (hallRentId != null) {
			hql += " and id <> ?";
		}
		hql += " and endDate > now() order by startDate asc";
		List<DatePair> busyDatePairs;
		if (hallRentId != null) {
			busyDatePairs = find(hql, hallId, hallRentId);
		} else {
			busyDatePairs = find(hql, hallId);
		}
		List<DatePair> idleDatePairs = new ArrayList<DatePair>(busyDatePairs.size() + 1);
		Date beginDate = new Date();
		for (DatePair idp : busyDatePairs) {
			Date endDate = idp.getStartDatetime();
			if (isBefore(beginDate, endDate)) {
				idleDatePairs.add(new DatePair(beginDate, endDate));
			}
			beginDate = idp.getEndDatetime();
		}
		Date endDate = setTime(beginDate, 23, 59, 59);
		if (isBefore(beginDate, endDate)) {
			idleDatePairs.add(new DatePair(beginDate, endDate));
		}

		return idleDatePairs;
	}

	private boolean isBefore(Date d1, Date d2) {
		return d1.before(d2);
	}

	private Date setTime(Date beginDate, int hour, int min, int sec) {
		Calendar instance = Calendar.getInstance();
		instance.setTime(beginDate);
		instance.set(Calendar.HOUR_OF_DAY, hour);
		instance.set(Calendar.MINUTE, min);
		instance.set(Calendar.SECOND, sec);
		return instance.getTime();
	}

	public List<HallCurrStatus> obtainLastestStatus() {
		String hql = "from " + entityClass.getSimpleName() + " where hallId=? and ? >= startDate and ? <= endDate";
		List<Hall> all = hallDao.getAll();
		Date nowDate = new Date();
		List<HallCurrStatus> result = new ArrayList<HallCurrStatus>(all.size());
		for (Hall hall : all) {
			HallRent r = findUnique(hql, hall.getId(), nowDate, nowDate);
			HallCurrStatus hallStatus = new HallCurrStatus();
			hallStatus.setHall(hall.getName());
			if ((Constants.STATUS_BUSY + "").equalsIgnoreCase(hall.getStatus())) {
				hall.setStatus(Constants.STATUS_OK + "");
			}
			hallStatus.setStatus(hall.getStatus());
			if (r != null) {
				hallStatus.setStartDate(r.getStartDate());
				hallStatus.setEndDate(r.getEndDate());
				hallStatus.setDeadName(r.getRegistration().getDeadName());
				hallStatus.setStatus(Constants.STATUS_BUSY + "");
				if (!(Constants.STATUS_INVALID + "").equalsIgnoreCase(hall.getStatus())) {
					hall.setStatus(Constants.STATUS_BUSY + "");
					hallDao.save(hall);
				}
			}
			result.add(hallStatus);
		}

		return result;
	}

	public List<HallStatisticsDisplayItem> searchByYear(int startYear, int endYear, boolean groupByHall) {
		if (!groupByHall)
			return getJdbcTemplate()
					.query("SELECT HALL,YEAR(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_HALL_RENT WHERE YEAR(START_DATE)>=? AND YEAR(START_DATE) <= ? GROUP BY YEAR(START_DATE)",
							reportRowMap, startYear, endYear);
		else
			return getJdbcTemplate()
					.query("SELECT HALL,YEAR(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM   T_HALL_RENT WHERE YEAR(START_DATE)>=? AND YEAR(START_DATE) <= ?  GROUP BY HALL,YEAR(START_DATE) ORDER BY HALL DESC",
							reportRowMap, startYear, endYear);
	}

	public List<HallStatisticsDisplayItem> searchByMon(int startYear, int startMon, int endYear, int endMon,
			boolean groupByHall) {
		if (!groupByHall)
			return getJdbcTemplate()
					.query("SELECT HALL,CONCAT(YEAR(START_DATE),'-',MONTH(START_DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_HALL_RENT WHERE (YEAR(START_DATE)>=?  AND MONTH(START_DATE) >= ?) AND (YEAR(START_DATE) <= ? AND MONTH(START_DATE) <= ?) GROUP BY YEAR(START_DATE), MONTH(START_DATE)",
							reportRowMap, startYear, startMon, endYear, endMon);
		else
			return getJdbcTemplate()
					.query("  SELECT HALL,CONCAT(YEAR(START_DATE),'-',MONTH(START_DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_HALL_RENT WHERE (YEAR(START_DATE)>=?  AND MONTH(START_DATE) >= ?) AND (YEAR(START_DATE) <= ? AND MONTH(START_DATE) <= ?) GROUP BY HALL,YEAR(START_DATE),MONTH(START_DATE) ORDER BY HALL DESC",
							reportRowMap, startYear, startMon, endYear, endMon);
	}

	public List<HallStatisticsDisplayItem> searchByDate(Date startDate, Date endDate, boolean groupByHall) {
		if (!groupByHall)
			return getJdbcTemplate()
					.query(" SELECT HALL,DATE(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_HALL_RENT  WHERE date(START_DATE)>=DATE(?) AND DATE(START_DATE) <= DATE(?) GROUP BY DATE(START_DATE)",
							reportRowMap, startDate, endDate);
		else
			return getJdbcTemplate()
					.query(" SELECT HALL,DATE(START_DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_HALL_RENT WHERE date(START_DATE)>=DATE(?) AND date(START_DATE) <= DATE(?)  GROUP BY HALL,DATE(START_DATE) ORDER BY HALL",
							reportRowMap, startDate, endDate);
	}

	ParameterizedRowMapper<HallStatisticsDisplayItem> reportRowMap = new ParameterizedRowMapper<HallStatisticsDisplayItem>() {
		public HallStatisticsDisplayItem mapRow(ResultSet rs, int paramInt) throws SQLException {
			HallStatisticsDisplayItem c = new HallStatisticsDisplayItem();
			c.setCount(rs.getInt("COUNT"));
			c.setDateString(rs.getString("DATESTRING"));
			c.setHallName(rs.getString("HALL"));
			return c;
		}
	};

	public List<HallRent> findUnpaiedByBiz(String bizNo) {
		return find("from HallRent where paied = false and registration.bizNo = ?", bizNo);
	}

	public void setAsPaied(Long chargeId) {
		HallRent hallRent = get(chargeId);
		if (null != hallRent) {
			hallRent.setPaied(true);
		}

	}

}
